package cn.doitedu.day07

import org.apache.spark.sql.SparkSession

object T20_SQLGameCount {

  def main(args: Array[String]): Unit = {
    val days = 3
    val spark = SparkSession.builder().appName(this.getClass.getSimpleName)
      .master("local[4]")
      .getOrCreate()

    //创建DataFrame
    val df = spark.read
      .csv("data/game.txt")
      .toDF("uid", "gid", "zid", "money", "os")


    df.createTempView("v_game")

    val res = spark.sql(
      s"""
        |select
        |    gid,
        |    zid,
        |    sum(money) money
        |from
        |(
        |    select
        |        gid,
        |        zid,
        |        sum(money) money
        |    from
        |    (
        |        select
        |            gid,
        |            zid,
        |            ceil(random() * 4) ri,
        |            money
        |        from
        |            v_game
        |    )
        |    group by gid, zid, ri
        |)
        |group by gid, zid
        |""".stripMargin)

    res.show()



  }

}
